package main

import (
	"database/sql"
	"fmt"
	"github.com/yourusername/gox/pkg/runtime"
	"time"
)

type User struct {
	ID        int
	Name      string
	Email     string
	Status    string
	CreatedAt time.Time
}

// 示例1: 简单参数插值
func GetUserByID(db *sql.DB, userID int) (*User, error) {
	query := func() *runtime.Query {
		__gox_sql_4_builder := runtime.NewQueryBuilder()
		__gox_sql_4_builder.AddText("SELECT id, name, email, status, created_at FROM users WHERE id =")
		__gox_sql_4_builder.AddParam(userID)
		__gox_sql_4 := __gox_sql_4_builder.Build()
		return __gox_sql_4
	}()

	row := db.QueryRow(query.String(), query.Args()...)
	var user User
	err := row.Scan(&user.ID, &user.Name, &user.Email, &user.Status, &user.CreatedAt)
	return &user, err
}

// 示例2: 多个参数和复杂表达式
func SearchUsers(db *sql.DB, keyword string, minAge int, limit int) ([]*User, error) {
	searchPattern := "%" + keyword + "%"
	minDate := time.Now().AddDate(-minAge, 0, 0)

	query := func() *runtime.Query {
		__gox_sql_3_builder := runtime.NewQueryBuilder()
		__gox_sql_3_builder.AddText("SELECT id, name, email, status, created_at FROM users WHERE (name ILIKE")
		__gox_sql_3_builder.AddParam(searchPattern)
		__gox_sql_3_builder.AddText("OR email ILIKE")
		__gox_sql_3_builder.AddParam(searchPattern)
		__gox_sql_3_builder.AddText(") AND created_at >=")
		__gox_sql_3_builder.AddParam(minDate)
		__gox_sql_3_builder.AddText("ORDER BY created_at DESC LIMIT")
		__gox_sql_3_builder.AddParam(limit)
		__gox_sql_3 := __gox_sql_3_builder.Build()
		return __gox_sql_3
	}()

	rows, err := db.Query(query.String(), query.Args()...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []*User
	for rows.Next() {
		var user User
		err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.Status, &user.CreatedAt)
		if err != nil {
			return nil, err
		}
		users = append(users, &user)
	}

	return users, nil
}

// 示例3: 条件逻辑在SQL外部处理
func GetUsersWithFilter(db *sql.DB, includeInactive bool, departmentID *int) ([]*User, error) {

	baseWhere := "WHERE deleted_at IS NULL"
	if !includeInactive {
		baseWhere += " AND status = 'active'"
	}

	var departmentFilter string
	if departmentID != nil {
		departmentFilter = fmt.Sprintf(" AND department_id = %d", *departmentID)
	}

	query := func() *runtime.Query {
		__gox_sql_2_builder := runtime.NewQueryBuilder()
		__gox_sql_2_builder.AddText("SELECT id, name, email, status, created_at FROM users")
		__gox_sql_2_builder.AddParam(baseWhere)
		__gox_sql_2_builder.AddParam(departmentFilter)
		__gox_sql_2_builder.AddText("ORDER BY name")
		__gox_sql_2 := __gox_sql_2_builder.Build()
		return __gox_sql_2
	}()

	rows, err := db.Query(query.String(), query.Args()...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []*User
	for rows.Next() {
		var user User
		err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.Status, &user.CreatedAt)
		if err != nil {
			return nil, err
		}
		users = append(users, &user)
	}

	return users, nil
}

// 示例4: 使用函数调用结果
func GetRecentUsers(db *sql.DB, days int) ([]*User, error) {
	query := func() *runtime.Query {
		__gox_sql_1_builder := runtime.NewQueryBuilder()
		__gox_sql_1_builder.AddText("SELECT id, name, email, status, created_at FROM users WHERE created_at >=")
		__gox_sql_1_builder.AddParam(time.Now().AddDate(0, 0, -days))
		__gox_sql_1_builder.AddText("ORDER BY created_at DESC")
		__gox_sql_1 := __gox_sql_1_builder.Build()
		return __gox_sql_1
	}()

	rows, err := db.Query(query.String(), query.Args()...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []*User
	for rows.Next() {
		var user User
		err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.Status, &user.CreatedAt)
		if err != nil {
			return nil, err
		}
		users = append(users, &user)
	}

	return users, nil
}

// 示例5: 复杂的JOIN查询
func GetUserWithPosts(db *sql.DB, userID int, postLimit int) (*User, error) {
	query := func() *runtime.Query {
		__gox_sql_0_builder := runtime.NewQueryBuilder()
		__gox_sql_0_builder.AddText("SELECT u.id, u.name, u.email, u.status, u.created_at, COUNT(p.id) as post_count FROM users u LEFT JOIN posts p ON u.id = p.user_id AND p.deleted_at IS NULL WHERE u.id =")
		__gox_sql_0_builder.AddParam(userID)
		__gox_sql_0_builder.AddText("GROUP BY u.id, u.name, u.email, u.status, u.created_at HAVING COUNT(p.id) >=")
		__gox_sql_0_builder.AddParam(postLimit)
		__gox_sql_0 := __gox_sql_0_builder.Build()
		return __gox_sql_0
	}()

	row := db.QueryRow(query.String(), query.Args()...)

	var user User
	var postCount int
	err := row.Scan(&user.ID, &user.Name, &user.Email, &user.Status, &user.CreatedAt, &postCount)
	if err != nil {
		return nil, err
	}

	return &user, nil
}

func main() {
	fmt.Println("GoX 高级示例程序")

}
